//
//  DataBaseHandler.m
//  EagleEye
//
//  Created by 18_李明明 on 15-1-28.
//  Copyright (c) 2015年 lanou3g.com 蓝鸥科技. All rights reserved.
//

#import "DataBaseHandler.h"
#import "QrObject.h"
#import "Bus.h"
#import "Express.h"
#import "Phone.h"
#import "Train.h"
@implementation DataBaseHandler

+ (DataBaseHandler *)shareInstance
{
    static DataBaseHandler *handler = nil;
    if (handler == nil) {
        handler = [[DataBaseHandler alloc] init];
    }
    return handler;
}
// 打开数据库
- (BOOL)openDB{
    NSString *path = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES)lastObject];
    path = [path stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding];
    NSString *dbPath = [path stringByAppendingPathComponent:@"DBflie.db"];
    int result = sqlite3_open([dbPath UTF8String], &dbpoint);
    return [self judgeResult:result text:@"打开"];
}

- (BOOL)judgeResult:(int)result text:(NSString *)text
{
    if (result == SQLITE_OK) {
        NSLog(@"%@SUCCESS", text);
        return  YES;
    }else {
        NSLog(@"%@DEFEAT", text);
        return NO;
    }
}
// 关闭数据库
- (BOOL)closeDB
{
    int result = sqlite3_close(dbpoint);
    return [self judgeResult:result text:@"关闭"];
}
// 创建表 插入数据
// 二维码
- (BOOL)createQR
{
    NSString *sql = @"create table QR(content text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建QR"];
}
- (BOOL)insertQR:(NSString *)content
{
    NSString *sql = [NSString stringWithFormat:@"insert into QR values('%@')", content];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入数据"];
}
// 快递
- (BOOL)createExpress
{
    NSString *sql = @"create table Express(number text primary key, company text, pinyin text)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建快递"];
}
- (BOOL)insertExpress:(NSString *)number Company:(NSString *)company pinyin:(NSString *)pinyin
{
    NSString *sql = [NSString stringWithFormat:@"insert into Express values('%@', '%@', '%@')", number, company, pinyin];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入快递"];
}
// 公交
- (BOOL)createBusOfLine
{
    NSString *sql = @"create table BusOfLine(line text, city text, combine text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建公交线路"];
}
- (BOOL)insertBusOfLine:(NSString *)line city:(NSString *)city combine:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"insert into BusOfLine values('%@', '%@', '%@')", line, city, combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入公交线路"];
}
- (BOOL)createBusOfChange
{
    NSString *sql = @"create table BusOfChange(from1 text, to1 text, city text, combine text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建公交换乘"];
    
}
- (BOOL)insertBusOfFrom:(NSString *)from to:(NSString *)to city:(NSString *)city combine:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"insert into BusOfChange values('%@', '%@', '%@', '%@')", from, to, city, combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入公交换乘"];
}
- (BOOL)createBusOfStation
{
    NSString *sql = @"create table BusOfStation(station text, city text, combine text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建公交站台"];
}
- (BOOL)insertBusOfStation:(NSString *)station city:(NSString *)city combine:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"insert into BusOfStation values('%@', '%@', '%@')", station, city, combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入公交站台"];
    
}
// 火车
- (BOOL)createTrainOfLine
{
    NSString *sql = @"create table TrainOfLine(line text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建火车线路"];
}
- (BOOL)insertTrainOfLine:(NSString *)line
{
    NSString *sql = [NSString stringWithFormat:@"insert into TrainOfLine values('%@')", line];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入火车线路"];
}
- (BOOL)createTrainOfChange
{
    NSString *sql = @"create table TrainOfChange(from1 text, to1 text, combine text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    NSLog(@"%d", result);
    return [self judgeResult:result text:@"创建火车乘车"];
}
- (BOOL)insertTrainOfFrom:(NSString *)from to:(NSString *)to combine:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"insert into TrainOfChange values('%@', '%@', '%@')", from, to, combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入火车乘车"];
}
// 手机
- (BOOL)createPhone
{
    NSString *sql = @"create table Phone(phone text primary key)";
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"创建手机"];
}
- (BOOL)insertPhone:(NSString *)phone
{
    NSString *sql = [NSString stringWithFormat:@"insert into Phone values('%@')", phone];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"插入手机"];
}

 //删除
- (BOOL)deleteQRWithContent:(NSString *)content
{
    NSString *sql = [NSString stringWithFormat:@"delete from QR where content = '%@'", content];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除QR"];
}
- (BOOL)deleteExpressWithNumber:(NSString *)number
{
    NSString *sql = [NSString stringWithFormat:@"delete from Express where number = '%@'", number];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除快递"];
}
- (BOOL)deleteBusOfLine:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"delete from BusOfLine where combine = '%@'", combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除公交路线"];
}
- (BOOL)deleteBusOfFromAndTo:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"delete from BusOfChange where combine = '%@'", combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除公交路线"];
}
- (BOOL)deleteBusOfStation:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"delete from BusOfStation where combine = '%@'", combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除公交路线"];
}
- (BOOL)deleteTrainOfLine:(NSString *)line
{
    NSString *sql = [NSString stringWithFormat:@"delete from TrainOfLine where line = '%@'", line];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除火车"];
    
}
- (BOOL)deleteTrainOfStation:(NSString *)combine
{
    NSString *sql = [NSString stringWithFormat:@"delete from TrainOfChange where combine = '%@'", combine];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除火车"];
}
- (BOOL)deletePhone:(NSString *)phone
{
    NSString *sql = [NSString stringWithFormat:@"delete from Phone where phone = '%@'", phone];
    int result = sqlite3_exec(dbpoint, [sql UTF8String], NULL, NULL, NULL);
    return [self judgeResult:result text:@"删除手机"];
}
// 查找
- (NSMutableArray *)selectQR
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from QR";
    int result = sqlite3_prepare_v2(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    NSMutableArray *qrArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *contentChar = sqlite3_column_text(stmt, 0);
            NSString *content = [NSString stringWithUTF8String:(const char *)contentChar];
            QrObject *qr = [[QrObject alloc] init];
            qr.content = content;
            [qrArr addObject:qr];
            
        }
    }
    sqlite3_finalize(stmt);
    return qrArr;
}
- (NSMutableArray *)selectExpress
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from Express";
    NSMutableArray *expressArr = [NSMutableArray array];
    int result = sqlite3_prepare_v2(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *numberChar = sqlite3_column_text(stmt, 0);
            NSString *number = [NSString stringWithUTF8String:(const char *)numberChar];
            const unsigned char *companyChar = sqlite3_column_text(stmt, 1);
            NSString *company = [NSString stringWithUTF8String:(const char *)companyChar];
            const unsigned char *pinyinChar = sqlite3_column_text(stmt, 2);
            NSString *pinyin = [NSString stringWithUTF8String:(const char *)pinyinChar];
            Express *express = [[Express alloc] init];
            express.expressNumber = number;
            express.company = company;
            express.pinyin = pinyin;
            [expressArr addObject:express];
        }
    }
    sqlite3_finalize(stmt);
    return expressArr;
}
- (NSMutableArray *)selectBusOfLine
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from BusOfLine";
    NSMutableArray *busArr = [NSMutableArray array];
    int result = sqlite3_prepare_v2(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *lineChar = sqlite3_column_text(stmt, 0);
            NSString *line = [NSString stringWithUTF8String:(const char *)lineChar];
            const unsigned char *cityChar = sqlite3_column_text(stmt, 1);
            NSString *city = [NSString stringWithUTF8String:(const char *)cityChar];
            const unsigned char *combineChar = sqlite3_column_text(stmt, 2);
            NSString *combine1 = [NSString stringWithUTF8String:(const char *)combineChar];
            Bus *bus = [[Bus alloc] init];
            bus.line = line;
            bus.city = city;
            bus.combine1 = combine1;
            [busArr addObject:bus];
        }
    }
    sqlite3_finalize(stmt);
    return busArr;
}
- (NSMutableArray *)selectBusOfChange
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from BusOfChange";
    NSMutableArray *busArr = [NSMutableArray array];
    int result = sqlite3_prepare(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *fromChar = sqlite3_column_text(stmt, 0);
            NSString *from = [NSString stringWithUTF8String:(const char *)fromChar];
            const unsigned char *toChar = sqlite3_column_text(stmt, 1);
            NSString *to = [NSString stringWithUTF8String:(const char *)toChar];
            const unsigned char *cityChar = sqlite3_column_text(stmt, 2);
            NSString *city = [NSString stringWithUTF8String:(const char *)cityChar];
            const unsigned char *combineChar = sqlite3_column_text(stmt, 3);
            NSString *combine2 = [NSString stringWithUTF8String:(const char *)combineChar];
            Bus *bus = [[Bus alloc] init];
            bus.from = from;
            bus.to = to;
            bus.city = city;
            bus.combine2 = combine2;
            [busArr addObject:bus];
            
        }
    }
    sqlite3_finalize(stmt);
    return busArr;
}
- (NSMutableArray *)selectBusOfStation
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from BusOfStation";
    int result = sqlite3_prepare(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    NSMutableArray *busArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *stationChar = sqlite3_column_text(stmt, 0);
            NSString *station = [NSString stringWithUTF8String:(const char *)stationChar];
            const unsigned char *cityChar = sqlite3_column_text(stmt, 1);
            NSString *city = [NSString stringWithUTF8String:(const char *)cityChar];
            const unsigned char *combineChar = sqlite3_column_text(stmt, 2);
            NSString *combine3 = [NSString stringWithUTF8String:(const char *)combineChar];
            Bus *bus = [[Bus alloc] init];
            bus.station = station;
            bus.city = city;
            bus.combine3 = combine3;
            [busArr addObject:bus];
        }
    }
    sqlite3_finalize(stmt);
    return busArr;
}
- (NSMutableArray *)selectTrainOfLine
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from TrainOfLine";
    int result = sqlite3_prepare(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    NSMutableArray *trainArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *lineChar = sqlite3_column_text(stmt, 0);
            NSString *line = [NSString stringWithUTF8String:(const char *)lineChar];
            Train *train = [[Train alloc] init];
            train.line = line;
            [trainArr addObject:train];
        }
    }
    sqlite3_finalize(stmt);
    return trainArr;
}
- (NSMutableArray *)selectTrainOfChange
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from TrainOfChange";
    int result = sqlite3_prepare(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    NSMutableArray *trainArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *fromChar = sqlite3_column_text(stmt, 0);
            NSString *from = [NSString stringWithUTF8String:(const char *)fromChar];
            const unsigned char *toChar = sqlite3_column_text(stmt, 1);
            NSString *to = [NSString stringWithUTF8String:(const char *)toChar];
            const unsigned char *combineChar = sqlite3_column_text(stmt, 2);
            NSString *combine = [NSString stringWithUTF8String:(const char *)combineChar];
            Train *train = [[Train alloc] init];
            train.from = from;
            train.to = to;
            train.combine1 = combine;
            [trainArr addObject:train];
            
        }
    }
    sqlite3_finalize(stmt);
    return trainArr;
}
- (NSMutableArray *)selectPhone
{
    sqlite3_stmt *stmt = nil;
    NSString *sql = @"select *from Phone";
    int result = sqlite3_prepare(dbpoint, [sql UTF8String], -1, &stmt, NULL);
    NSMutableArray *phoneArr = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *phoneChar = sqlite3_column_text(stmt, 0);
            NSString *phoneNumber = [NSString stringWithUTF8String:(const char *)phoneChar];
            Phone *phone = [[Phone alloc] init];
            phone.phoneNumber = phoneNumber;
            [phoneArr addObject:phone];
        }
    }
    sqlite3_finalize(stmt);
    return phoneArr;
}




@end
